﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Media;
using System.Windows;
using System.Windows.Controls;

namespace Projet
{
    public class Bdd
    {
        MaBaseDataContext bdd = new MaBaseDataContext();
        //pour vérifier si l'user a rentré le bon pass
        public bool LogMe(int id, string pass)
        {
            var test = (from c in bdd.Users
                                where c.id == id
                                select c.password).First();
             if (test == pass) { return true; } 
             else { return false; }

        }

        public string[] GetUserInfo(int id) // pour la recherche d'un user
        {
            try
            {
                string[] infos = new string[7];
                var test = (from c in bdd.Users
                            where c.id == id
                            select new { c.campus, c.name, c.firstname, c.privilege, c.cursus, c.classe  }).First();
                infos[0] = test.name;
                infos[1] = test.firstname;
                infos[2] = test.campus;
                infos[3] = test.privilege.ToString();
                infos[4] = test.cursus;
                infos[5] = test.classe.ToString();
             
                return infos;
            }
            catch
            {
                return null ;
            }

        }
        
        public void DeleteUser(int user)  // effacer un user via son string
        {
            try
            {
                var userToDelete = (from p in bdd.Users
                                    where p.id.Equals(user)
                                    select p).First();
                bdd.Users.DeleteOnSubmit(userToDelete);
                bdd.SubmitChanges();
            }
            catch { }
        }

        public void ModifyUser(int user, string name, string firstname, string campus, int privilege, string password, string cursus, int classe)
        {
            try
            {
                var modify = (from p in bdd.Users
                              where p.id.Equals(user)
                              select p).First();
                modify.privilege = privilege + 1;
                modify.name = name;
                modify.campus = campus;
                modify.firstname = firstname;
                modify.cursus = cursus;
                modify.classe = classe;
                if (modify.password != "") { modify.password = password; }
                bdd.SubmitChanges();
            }
            catch { MessageBox.Show("Error: User has not been modified."); }
        }

        public  List<String> GetCampus()
        {
            var campus = (from p in bdd.Campus
                          orderby p.Campus1 ascending
                    select p.Campus1).ToList();
            return campus;

                    
        }

        public void AddUser(string name,string firstname,string campus,int privilege, string password, string cursus, int classe)
        {
            try
            {
                User usr = new User();
                usr.name = name;
                usr.firstname = firstname;
                usr.campus = campus;
                usr.privilege = privilege;
                usr.password = password;
                usr.classe = classe;
                usr.cursus = cursus;
                bdd.Users.InsertOnSubmit(usr);
                bdd.SubmitChanges();

            }
            catch { MessageBox.Show("Missing parameters"); }
            
            
        }

        public List<NewUsers> getLastUserId()  // pour obtenir les 15 derniers utilisateurs enregistrés
        {
           return (from p in bdd.Users
                    orderby p.id descending
                    select new  NewUsers()
                    { 
                        privilege = Convert.ToInt32(p.privilege),
                        name = p.name,
                        id = p.id,
                        firstname = p.firstname,
                        campus = p.campus,
                        cursus = p.cursus,
                        classe = Convert.ToInt32(p.classe),
                    }).Take(15).ToList();  
        }

        public void AddCampus(string newCampus)
        {

            try
            {
                if (newCampus != "")
                {
                    Campus campus = new Campus();
                    campus.Campus1 = newCampus;
                    bdd.Campus.InsertOnSubmit(campus);
                    bdd.SubmitChanges();
                }

            }
            catch { MessageBox.Show("Error"); }
        }

        public List<NewUsers> GetCampusMembers(string cmp)
        {
            return (from p in bdd.Users
                    where p.campus == cmp
                    orderby p.privilege ascending
                    select new NewUsers()
                    {
                        privilege = Convert.ToInt32(p.privilege),
                        name = p.name,
                        id = p.id,
                        firstname = p.firstname,
                        campus = p.campus,
                        cursus = p.cursus,
                        
                    }).ToList();

        }

        public string[] GetCampusManager(string cmp)
        {
            try
            {
                string[] infos = new string[2];
                var cm = (from c in bdd.Users 
                          where Convert.ToInt32(c.privilege) <= 2 && c.campus == cmp
                          select new { c.name, c.firstname}).First();
                infos[0] = cm.name;
                infos[1] = cm.firstname;
              
                return infos;
            }
            catch
            {
                return null;
            }
        }

        public void DeleteCampus(string campus)
        {
            try
            {
                Campus campusToDelete = (from p in bdd.Campus
                                         where p.Campus1.Equals(campus)
                                         select p).First();
                bdd.Campus.DeleteOnSubmit(campusToDelete);
                bdd.SubmitChanges();

                List<User> deleteUsersCampus = new List<User>();
             deleteUsersCampus = (from u in bdd.Users
                                     where u.campus == campus
                                     select u).ToList();
                foreach (User u in deleteUsersCampus)
                {
                    u.campus = "";
                }
                bdd.SubmitChanges();
                
            }
            catch (Exception)
            {
               MessageBox.Show("Le Campus sélectionné est introuvable");
           }
        }

        //les cursus: ------------------------------------------------------------------------------------>>

        public List<String> getCursus()
        {
            var cursus = (from p in bdd.Cursus
                          orderby p.CursusName ascending
                          select p.CursusName).ToList();
            return cursus;
        }


        public IEnumerable<string> GetCampusFromCursus(string cursus)
        {
           
            List<string> avecDoublons = (from p in bdd.Users
                           where p.cursus == cursus
                          orderby p.campus ascending
                          select p.campus).ToList();

            IEnumerable<string> classes = avecDoublons.Distinct();
            return classes;
        }

        public IEnumerable<String> GetClassesFromCampus(string campus, string cursus)
        {

            List<string> avecDoublons = (from p in bdd.Users
                                         where p.campus == campus && p.cursus == cursus
                                         orderby p.classe ascending
                                         select p.classe.ToString()).ToList();

            IEnumerable<String> classes = avecDoublons.Distinct(); // permet de supprimer les doublons de la liste
            return classes;
        }

      
        public List<String> GetUsersFromClasse(int classe, string campus, string cursus)
        {
            if (classe > 0)
            {
                List<String> users = (from p in bdd.Users
                                      where p.classe == classe && p.campus == campus && p.cursus == cursus
                                      orderby p.id ascending
                                      select p.id + ": " + p.firstname + " " + p.name).ToList();
                return users;
            }
            else
            {
                List<String> users = (from p in bdd.Users
                                      where p.campus == campus && p.cursus == cursus
                                      orderby p.id ascending
                                      select p.id + ": " + p.firstname + " " + p.name + "  Cl:" + p.classe + " Priv:" + p.privilege).ToList();
                return users;
            }
            
        }

      public IEnumerable<String>  GetCursusFromCampus(string campus) 
      {
          var avecDoublons = (from p in bdd.Users
                              where p.campus.Equals(campus)
                              orderby p.cursus
                              select p.cursus.ToString()).ToList();

          IEnumerable<String> cursus = avecDoublons.Distinct();
          return cursus;
        }

        public void Cursus(string action, string cursus) 
        {
            if (action == "delete")
            {
                //effacer les cursus de bdd.Cursus
                var cursusToDelete = (from p in bdd.Cursus
                                    where p.CursusName.Equals(cursus)
                                    select p).First();

               //effacer les cursus de la liste des users (bdd.Users)
                List<User> deleteUsersCursus = new List<User>();
                deleteUsersCursus = (from u in bdd.Users
                                     where u.cursus == cursus
                                     select u).ToList();
                foreach (User u in deleteUsersCursus)
                {
                    u.cursus = "";
                }
                bdd.Cursus.DeleteOnSubmit(cursusToDelete);
                MessageBox.Show("Cursus has been deleted");
            }
                else if (action == "add")
                {
                Cursus cur = new Cursus();
                cur.CursusName = cursus;
                bdd.Cursus.InsertOnSubmit(cur);
                }
                bdd.SubmitChanges();
            }

        //sur les professeurs
        public IEnumerable<String> GetSubjects()
        {
            var subjectList = (from p in bdd.Matieres
                                orderby p.Matiere1
                                select p.Matiere1.ToString()).ToList();

            IEnumerable<String> subject = subjectList.Distinct();
            return subject;
        }

        public IEnumerable<String> GetTeacher(string campus, int classe)
        {
            var teacherList = (from p in bdd.Teachers
                                where p.Campus.Equals(campus) && (p.Classe.Equals(classe) || p.Classe.Equals(0))
                                orderby p.Matiere
                                select p.Matiere.ToString()).ToList();
            IEnumerable<String> subject = teacherList.Distinct();
            return subject;
        }

        public List<int> GetTeachers() // retourne tous les prof enregistrés (tous les users de level 3)
        {
            return (from p in bdd.Users
                    where p.privilege == 3
                    orderby p.id
                    select p.id).ToList();
        }

        public int GetTeacherId(string subject, string campus, int classe) // retourne l'id d'un prof en fonction de sa classe de son campus et de sa matière
        {
            return (from p in bdd.Teachers
                    where p.Matiere == subject && (p.Classe == classe || p.Classe == 0) && p.Campus == campus
                    select p.TeacherId).First();
        }

        public NewUsers GetTeacherInfos(int id) // nom et prénom d'un prof en fonction de l'id
        {
            return (from p in bdd.Users
                    where p.id == id && p.privilege == 3
                    select new NewUsers
                    {
                        id = p.id,
                        name = p.name,
                        firstname = p.firstname,
                    }).First();
        }

        public void addSubject(string subject ,string cursus, string campus, int classe, int TeacherID) // aajouter une matière
        {
            Matiere sub = new Matiere();
            try
            {
                var o = (from p in bdd.Matieres
                                 where p.Matiere1.Equals(subject)
                                 select p).First();
            }
            catch // si elle n'existe pas déjà alors on la crée
            {
                sub.Matiere1 = subject;
                sub.Cursus = cursus;
                sub.Campus = campus;
                sub.Classe = classe;
                bdd.Matieres.InsertOnSubmit(sub);
                
            }
            Teacher teacher = new Teacher();
            teacher.Matiere = subject;
            teacher.Campus = campus;
            teacher.Classe = classe;
            teacher.TeacherId = TeacherID;
            bdd.Teachers.InsertOnSubmit(teacher);
            bdd.SubmitChanges();
        }

        public void addSubject(string subject, string cursus, string campus, int classe) // aajouter une matière
        {
            Matiere sub = new Matiere();
            try
            {
                var o = (from p in bdd.Matieres
                         where p.Matiere1.Equals(subject) && p.Cursus == cursus && p.Classe == classe && p.Campus == campus
                         select p).First();
            }
            catch // si elle n'existe pas déjà alors on la crée
            {
                sub.Campus = campus;
                sub.Classe = classe;
                sub.Cursus = cursus;
                sub.Matiere1 = subject;
                bdd.Matieres.InsertOnSubmit(sub);
            }
           
            bdd.SubmitChanges();
        }
       
        public void delSubject(string subject) // effacer une matière
        {
            var ToDelete2 = (from p in bdd.Matieres
                             where p.Matiere1.Equals(subject) 
                             select p).First();
            bdd.Matieres.DeleteOnSubmit(ToDelete2);

            var ToDelete = from p in bdd.Teachers
                           where p.Matiere.Equals(subject)
                           select p;
           
            bdd.Teachers.DeleteAllOnSubmit(ToDelete);
            bdd.SubmitChanges();
        }

        public void delSubject(string subject, int classe, string campus) // effacer une matière
        {
            
                var ToDelete = (from p in bdd.Teachers
                                where p.Matiere.Equals(subject) && p.Classe == classe && p.Campus == campus
                                select p).First();
          
           
                bdd.Teachers.DeleteOnSubmit(ToDelete);  
            
            bdd.SubmitChanges();
        }

        public void modifyTeacher(string cursus, string campus, string subject, int teacherid)
        {
            try
            {
                var modify = (from p in bdd.Teachers
                              where p.Matiere.Equals(subject) && p.Campus == campus && p.Campus == campus
                              select p).First();

                modify.TeacherId = teacherid;
               
                bdd.SubmitChanges();
            }
            catch { MessageBox.Show("Error: Teacher has not been modified."); }
        }

        public int GetHoursFromSubject(string subject, string cursus)
        {
            try
            {
                return (from p in bdd.Matieres
                        where p.Matiere1.EndsWith(subject) && p.Cursus == cursus
                        select p.Hours).First();
            
            }
            catch { return 0; }
        }

        public void modifyHours(int hour, string subject, string cursus)
        {
            try
            {
                var modify = (from p in bdd.Matieres
                              where p.Matiere1.EndsWith(subject) && p.Cursus == cursus
                              select p).First();

                modify.Hours = hour;
                bdd.SubmitChanges();
            }
            catch { MessageBox.Show("Unknown error"); }
        }

        public int countHoursAllocatedToSubject(string subject, string cursus)
        {
            try
            {
                int i1 = (from p in bdd.Weeks
                          where p.Mon.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Monday = p.Mon }).Count();
                int i2 = (from p in bdd.Weeks
                          where p.Tue.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Tuesday = p.Tue }).Count();
                int i3 = (from p in bdd.Weeks
                          where p.Wed.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Wednesday = p.Wed }).Count();
                int i4 = (from p in bdd.Weeks
                          where p.Thu.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Thursday = p.Thu }).Count();
                int i5 = (from p in bdd.Weeks
                          where p.Fri.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Friday = p.Fri }).Count();
                int i6 = (from p in bdd.Weeks
                          where p.Sat.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Saturday = p.Sat }).Count();
                int i7 = (from p in bdd.Weeks
                          where p.Sun.EndsWith(subject) && p.Cursus == cursus
                          select new Weeks() { Sunday = p.Sun }).Count();
                return i1 + i2 + i3 + i4 + i5 + i6 + i7;
            }
            catch { return 0; }
        }

// Gestion des plannings ------------------------------------------------------------------>

        public List<Weeks> GetPlanning(string cmp, string cursus,int week, int classe,int year)
        {
            try
            {
                string[] tabWeeks = new string[2];
                List<Weeks> list =  (from p in bdd.Weeks
                        where p.Campus == cmp && p.Cursus == cursus && ( p.Classe == classe || p.Classe == 0) && p.Year == year && p.Week1 == week
                        select new Weeks()
                        {
                            Hour = p.Hour,
                            Monday = p.Mon,
                            Tuesday = p.Tue,
                            Wednesday = p.Wed,
                            Thursday = p.Thu,
                            Friday = p.Fri,
                            Saturday = p.Sat,
                            Sunday = p.Sun,
                        }).Take(15).ToList();
                foreach (Weeks w in list)
                {
                    try { tabWeeks = w.Monday.Split(':'); w.Monday = tabWeeks[1]; }
                    catch { }

                    try { tabWeeks = w.Tuesday.Split(':'); w.Tuesday = tabWeeks[1]; }
                    catch { }
                    try { tabWeeks = w.Wednesday.Split(':'); w.Wednesday = tabWeeks[1]; }
                    catch { }
                    try { tabWeeks = w.Thursday.Split(':'); w.Thursday = tabWeeks[1]; }
                    catch { }
                    try { tabWeeks = w.Friday.Split(':'); w.Friday = tabWeeks[1]; }
                    catch { }
                    try { tabWeeks = w.Saturday.Split(':'); w.Saturday = tabWeeks[1]; }
                    catch { }
                    try { tabWeeks = w.Sunday.Split(':'); w.Sunday = tabWeeks[1]; }
                    catch { }
                }
                return list;
            }
            catch { return null; }
        }

        public void CreatePlanning(string cursus, string campus, int classe, int week, int year)
        {
            try
            {
                var users = (from p in bdd.Weeks
                             where p.Week1 == week && p.Campus == campus && p.Cursus == cursus && p.Year == year && p.Classe == classe
                             select p).First();
                MessageBox.Show("This week alreay exists");
            }
            catch
            {

                int h = 7;
                for (int i = 1; i <= 15; i++)
                {
                    Week w = new Week();
                    if (h <= 11) { w.Hour = h.ToString() + "-" + (h + 1).ToString(); }
                    else if (h == 12) { w.Hour = h.ToString() + "-" + "1"; h = 0; }
                    h++;
                    w.Classe = classe;
                    w.Week1 = week;
                    w.Cursus = cursus;
                    w.Campus = campus;
                    w.Year = year;
                    bdd.Weeks.InsertOnSubmit(w);
                    bdd.SubmitChanges();
                }
                MessageBox.Show("New week added");
            }
            
        }

        public void ModifyPlan(string hour, int column, string mat, string campus, string cursus, int classe,int week, int year)
        {

            var existingCust = (from c in bdd.Weeks
                                where c.Hour == hour && c.Campus == campus && ( c.Classe == classe || c.Classe == 0) && c.Week1 == week && c.Year == year && c.Cursus == cursus
                                select c).First();
            if (column == 1)
            {
                existingCust.Mon = mat;
            }
            else if (column == 2)
            {
                existingCust.Tue = mat;
            }
            else if (column == 3)
            {
                existingCust.Wed = mat;
            }
            else if (column == 4)
            {
                existingCust.Thu = mat;
            }
            else if (column == 5)
            {
                existingCust.Fri = mat;
            }
            else if (column == 6)
            {
                existingCust.Sat = mat;
            }
            else if (column == 7)
            {
                existingCust.Sun = mat;
            }
            bdd.SubmitChanges();
        }

        public bool PrivilegesTest(string hour, int column, string mat, string campus, string cursus, int classe, int week, int year, int actualPrivilege)
        {
            string[] tabRecup = new string[2];
            try
            {
                var recuperation = (from c in bdd.Weeks
                                    where c.Hour == hour && c.Campus == campus && (c.Classe == classe || c.Classe == 0) && c.Week1 == week && c.Year == year && c.Cursus == cursus
                                    select c).First();
                if (column == 1)
                {
                    tabRecup = recuperation.Mon.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 2)
                {
                    tabRecup = recuperation.Tue.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 3)
                {
                    tabRecup = recuperation.Wed.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 4)
                {
                    tabRecup = recuperation.Thu.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 5)
                {
                    tabRecup = recuperation.Fri.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 6)
                {
                    tabRecup = recuperation.Sat.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                else if (column == 7)
                {
                    tabRecup = recuperation.Sun.Split(':');
                    if (actualPrivilege <= Convert.ToInt32(tabRecup[0]))
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (System.NullReferenceException)
            {
                return true;
            }
            catch (System.FormatException)
            {
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

      }

}
